﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Cocbook_DTO;
using Cocbook_DAL;

namespace Cocbook_DAL
{
    public class BillDAL
    {
        public bool CreateBill(Bill b)
        {
            DB.SetQuery("INSERT INTO Bill VALUES(@pid ,@cid ,@sid ,@date ,@sub, @paid, @type)");
            DB.AddParameters("pid", b.Project.ProjectID);
            DB.AddParameters("cid", b.Customer.CustomerID);
            DB.AddParameters("sid", b.Staff.StaffID);
            DB.AddParameters("date", b.Date);
            DB.AddParameters("sub", b.Total);
            DB.AddParameters("paid", b.Paid);
            DB.AddParameters("type", b.CheckOutType);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.CreateBill", e.ToString());
                return false;
            }
        }

        public bool UpdateBill(Bill b)
        {
            DB.SetQuery("UPDATE Bill Set ProjectID = @pid,CustomerID = @cid, StaffID = @sid, Date = @date, Total = @sub"+
                        ", Paid = @paid, CheckOutType = @type WHERE BillID = @bid");
            DB.AddParameters("bid", b.BillID);
            DB.AddParameters("pid", b.Project.ProjectID);
            DB.AddParameters("cid", b.Customer.CustomerID);
            DB.AddParameters("sid", b.Staff.StaffID);
            DB.AddParameters("date", b.Date);
            DB.AddParameters("sub", b.Total);
            DB.AddParameters("paid", b.Paid);
            DB.AddParameters("type", b.CheckOutType);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.UpdateBill", e.ToString());
                return false;
            }
        }

        public bool DeleteBill(int id)
        {
            DB.SetQuery("DELETE FROM Bill WHERE BillID = @id");
            DB.AddParameters("id", id);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.DeleteBill", e.ToString());
                return false;
            }
        }

        public int GetBillIDByDateTime(DateTime time)
        {
            try
            {
                DB.SetQuery("SELECT BillID FROM Bill WHERE Date = @date");
                DB.AddParameters("date", time);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                int max = 0;
                if (llo.Count > 0)
                {
                    max = Convert.ToInt16(llo[0][0]);
                }
                else "".Substring(2);
                return max;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.GetBillIDByDateTime", e.ToString());
                return 0;
            }
        }

        public Bill GetBillByID(int id)
        {
            try
            {
                DB.SetQuery("SELECT BillID, ProjectID, CustomerID, StaffID, Date, Total, Paid, CheckOutType FROM Bill WHERE BillID = @id");
                DB.AddParameters("id", id);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                Bill b = new Bill();
                if (llo.Count > 0)
                {
                    b.BillID = Convert.ToInt16(llo[0][0]);
                    b.Project = (new ProjectDAL()).GetProjectByID(Convert.ToInt16(llo[0][1]));
                    b.Customer = (new CustomerDAL()).GetCustomerById(Convert.ToInt16(llo[0][2]));
                    b.Staff = (new StaffDAL()).GetStaffByID(Convert.ToInt16(llo[0][3]));
                    b.Date = Convert.ToDateTime(llo[0][4]);
                    b.Total = Convert.ToDouble(llo[0][5]);
                    b.Paid = Convert.ToDouble(llo[0][6]);
                    b.CheckOutType = Convert.ToInt16(llo[0][7]);
                }
                else "".Substring(2);
                return b;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.GetBillByID", e.ToString());
                return null;
            }
        }

        public int GetMaxBillID()
        {
            try
            {
                DB.SetQuery("SELECT MAX(BillID)FROM Bill");
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                int max = 0;
                if (llo.Count > 0)
                {
                    max = Convert.ToInt16(llo[0][0]);
                }
                else "".Substring(2);
                return max;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.GetMaxBillID", e.ToString());
                return 0;
            }
        }

        public List<Bill> GetAllBillByFilter(int pid, int bid, int cid, int sid, DateTime date, double ftotal, double ltotal)
        {
            try
            {
                DB.SetQuery("SELECT BillID, ProjectID, CustomerID, StaffID, Date, Total, Paid, CheckOutType FROM Bill WHERE "+
                            "((ProjectID = @pid)or (@pid = 0)) and"+
                            "((BillID = @bid)or (@bid = 0)) and" +
                            "((CustomerID = @cid)or (@cid = 0)) and"+
                            "((StaffID = @sid) or (@sid = 0)) and"+
                            "((convert(nvarchar(30),Date,103)= @date) or (@date = '1/1/1800')) and"+
                            "((Total >= @ftotal)or (@ftotal = 0)) and"+
                            "((Total <= @ltotal)or (@ltotal = 0));");
                DB.AddParameters("pid", pid);
                DB.AddParameters("bid", bid);
                DB.AddParameters("cid", cid);
                DB.AddParameters("sid", sid);
                DB.AddParameters("date", date);
                DB.AddParameters("ftotal", ftotal);
                DB.AddParameters("ltotal", ltotal);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<Bill> B = new List<Bill>();
                Bill b;
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        b = new Bill();
                        b.BillID = Convert.ToInt16(lo[0]);
                        b.Project = (new ProjectDAL()).GetProjectByID(Convert.ToInt16(lo[1]));
                        b.Customer = (new CustomerDAL()).GetCustomerById(Convert.ToInt16(lo[2]));
                        b.Staff = (new StaffDAL()).GetStaffByID(Convert.ToInt16(lo[3]));
                        b.Date = Convert.ToDateTime(lo[4]);
                        b.Total = Convert.ToDouble(lo[5]);
                        b.Paid = Convert.ToDouble(lo[6]);
                        b.CheckOutType = Convert.ToInt16(lo[7]);
                        B.Add(b);
                    }
                }
                else "".Substring(2);
                return B;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.GetAllBillByFilter", e.ToString());
                return null;
            }
        }


        public List<Bill> GetAllBillByProjectID(int id)
        {
            try
            {
                DB.SetQuery("SELECT BillID, ProjectID, CustomerID, StaffID, Date, Total, Paid, CheckOutType FROM Bill WHERE ProjectID = @id");
                DB.AddParameters("id", id);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<Bill> B = new List<Bill>();
                Bill b;
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        b = new Bill();
                        b.BillID = Convert.ToInt16(lo[0]);
                        b.Project = (new ProjectDAL()).GetProjectByID(Convert.ToInt16(lo[1]));
                        b.Customer = (new CustomerDAL()).GetCustomerById(Convert.ToInt16(lo[2]));
                        b.Staff = (new StaffDAL()).GetStaffByID(Convert.ToInt16(lo[3]));
                        b.Date = Convert.ToDateTime(lo[4]);
                        b.Total = Convert.ToDouble(lo[5]);
                        b.Paid = Convert.ToDouble(lo[6]);
                        b.CheckOutType = Convert.ToInt16(lo[7]);
                        B.Add(b);
                    }
                }
                else "".Substring(2);
                return B;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.GetBillByProjectID", e.ToString());
                return null;
            }
        }

        public List<Bill> GetAllBill()
        {
            try
            {
                DB.SetQuery("SELECT BillID, ProjectID, CustomerID, StaffID, Date, Total, Paid, CheckOutType FROM Bill");
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<Bill> B = new List<Bill>();
                Bill b;
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        b = new Bill();
                        b.BillID = Convert.ToInt16(lo[0]);
                        b.Project = (new ProjectDAL()).GetProjectByID(Convert.ToInt16(lo[1]));
                        b.Customer = (new CustomerDAL()).GetCustomerById(Convert.ToInt16(lo[2]));
                        b.Staff = (new StaffDAL()).GetStaffByID(Convert.ToInt16(lo[3]));
                        b.Date = Convert.ToDateTime(lo[4]);
                        b.Total = Convert.ToDouble(lo[5]);
                        b.Paid = Convert.ToDouble(lo[6]);
                        b.CheckOutType = Convert.ToInt16(lo[7]);
                        B.Add(b);
                    }
                }
                else "".Substring(2);
                return B;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.GetAllBill", e.ToString());
                return null;
            }
        }
    }
}
